﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using DAO;
using System.Web.Script.Services;
using System.Text;
using System.Xml;
using System.Web.Script.Serialization;

/// <summary>
/// Summary description for TextInfoItems
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class TextInfoItems : System.Web.Services.WebService
{

    public TextInfoItems()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml)]
    public XmlDocument LoadAll(int page, int pageSize, string sidx, string sord, string letter, int language_id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string q = "";

            if (letter == "") q = "SELECT count(id) as countid FROM dbo.Fn_GetAllTextInfoItems(0, 1000000, 'id', 'asc', 1)";
            else q = "SELECT count(id) as countid FROM dbo.Fn_GetAllTextInfoItems(0, 1000000, 'id', 'asc', 1)"
                + " WHERE UPPER(SUBSTRING(refference, 1, 1)) = '" + letter.ToUpper() + "'";
            using (SqlCommand cmd = new SqlCommand(q, conn))
            {
                SqlDataReader dr = null;
                int start = page * pageSize - pageSize;
                if (start < 0) start = 0;
                int limit = pageSize * page;

                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[5];
                pars[0] = new SqlParameter("start", start);
                pars[1] = new SqlParameter("limit", limit);
                pars[2] = new SqlParameter("order", sidx);
                pars[3] = new SqlParameter("orderdir", sord);
                pars[4] = new SqlParameter("language_id", language_id);

                int total = 0;
                dr = cmd.ExecuteReader();
                if (dr != null)
                {
                    if (dr.HasRows)
                    {
                        if (dr.Read()) Int32.TryParse(dr["countid"].ToString(), out total);
                    }
                }
                dr.Close();
                if (letter == "") cmd.CommandText = "SELECT * FROM dbo.Fn_GetAllTextInfoItems(@start, @limit, @order, @orderdir, @language_id) ORDER By " + sidx + " " + sord;
                else cmd.CommandText = "SELECT * FROM dbo.Fn_GetAllTextInfoItems(@start, @limit, @order, @orderdir, @language_id)"
                    + " WHERE UPPER(SUBSTRING(refference, 1, 1)) = '" + letter.ToUpper() + "' ORDER By " + sidx + " " + sord;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                XmlDocument d = new XmlDocument();
                if (dr1 != null)
                {
                    if (dr1.HasRows)
                    {
                        XmlDeclaration dec =
                            d.CreateXmlDeclaration("1.0", "utf-8", null);
                        d.AppendChild(dec);
                        XmlNode root = d.CreateElement("rows");
                        d.AppendChild(root);
                        XmlNode _page = d.CreateElement("page");
                        _page.InnerText = page.ToString();
                        root.AppendChild(_page);
                        XmlNode _total = d.CreateElement("total");
                        _total.InnerText = Math.Ceiling((double)total / pageSize).ToString();
                        root.AppendChild(_total);
                        XmlNode _records = d.CreateElement("records");
                        _records.InnerText = total.ToString();
                        root.AppendChild(_records);
                        XmlElement el;
                        while (dr1.Read())
                        {
                            el = d.CreateElement("row");
                            el.SetAttribute("id", dr1["id"].ToString());
                            XmlElement cell = d.CreateElement("cell");
                            cell.InnerText = dr1["id"].ToString();
                            el.AppendChild(cell);
                            cell = d.CreateElement("cell");
                            cell.InnerText = dr1["refference"].ToString();
                            el.AppendChild(cell);
                            cell = d.CreateElement("cell");
                            XmlCDataSection cdata = d.CreateCDataSection(dr1["text"].ToString());
                            cell.AppendChild(cdata);
                            el.AppendChild(cell);
                            root.AppendChild(el);
                        }
                    }
                }
                if (dr1 != null && !dr1.IsClosed) dr1.Close();
                return d;
            }
        }
    }

    public class TextBaseItemObj
    {
        public long _id { get; set; }
        public string _refference { get; set; }
        public string _text { get; set; }
        public int _language_id { get; set; }
    }
    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string getRefference(long id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * from dbo.Fn_GetTextInfo(@id)", conn))
            {
                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[1];
                pars[0] = new SqlParameter("id", id);

                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                TextBaseItemObj l = new TextBaseItemObj();
                if (dr1.Read())
                {
                    long _id = 0;
                    string refference = "";
                    string _text = "";
                    int lang_id = 0;
                    Int64.TryParse(dr1["id"].ToString(), out _id);
                    Int32.TryParse(dr1["language_id"].ToString(), out lang_id);
                    refference = dr1["refference"].ToString();
                    _text = dr1["text"].ToString();
                    l._id = _id;
                    l._refference = refference;
                    l._text = _text;
                    l._language_id = lang_id;
                }

                if (dr1 != null && !dr1.IsClosed) dr1.Close();

                List<TextBaseItemObj> lst = new List<TextBaseItemObj>();
                lst.Add(l);
                JavaScriptSerializer js = new JavaScriptSerializer();
                string strJSON = js.Serialize(l);
                return strJSON;
            }
        }
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string getTextBaseItemByLang(string refference, int language)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * from dbo.Fn_GetTextInfoByLanguage(@ref, @langid)", conn))
            {
                SqlParameter[] pars = new SqlParameter[2];
                pars[0] = new SqlParameter("ref", refference);
                pars[1] = new SqlParameter("langid", language);

                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                TextBaseItemObj l = new TextBaseItemObj();
                if (dr1.Read())
                {
                    long _id = 0;
                    string reff = "";
                    string _text = "";
                    int lang_id = 0;
                    Int64.TryParse(dr1["id"].ToString(), out _id);
                    Int32.TryParse(dr1["language_id"].ToString(), out lang_id);
                    refference = dr1["refference"].ToString();
                    _text = dr1["text"].ToString();
                    l._id = _id;
                    l._refference = reff;
                    l._text = _text;
                    l._language_id = lang_id;
                }

                if (dr1 != null && !dr1.IsClosed) dr1.Close();

                List<TextBaseItemObj> lst = new List<TextBaseItemObj>();
                lst.Add(l);
                JavaScriptSerializer js = new JavaScriptSerializer();
                string strJSON = js.Serialize(l);
                return strJSON;
            }
        }
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string updateRefference(long id, int _langid, string refference, string text)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string q = "";
            if (id != 0)
            {
                q = "SP_UpdateTextInfoItems";
            }
            else
            {
                q = "SP_InsTextInfo";
            }
            using (SqlCommand cmd = new SqlCommand(q, conn))
            {
                SqlParameter[] pars = new SqlParameter[4];
                pars[0] = new SqlParameter("id", id);
                pars[1] = new SqlParameter("langid", _langid);
                pars[2] = new SqlParameter("refference", refference);
                pars[3] = new SqlParameter("text", text);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                int val = cmd.ExecuteNonQuery();
                if (id != 0)
                    DAO.TextBaseFactory.updateDictionary(refference, _langid, text);
                cmd.Parameters.Clear();

                JavaScriptSerializer js = new JavaScriptSerializer();

                return (val > 0) ? js.Serialize("true") : js.Serialize("false");
            }
        }
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string insRefference(int _langid, string refference, string text)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsTextInfo", conn))
            {
                SqlParameter[] pars = new SqlParameter[4];
                pars[0] = new SqlParameter("id", 3);
                pars[1] = new SqlParameter("langid", _langid);
                pars[2] = new SqlParameter("refference", refference);
                pars[3] = new SqlParameter("text", text);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                JavaScriptSerializer js = new JavaScriptSerializer();

                return (val > 0) ? js.Serialize("true") : js.Serialize("false");
            }
        }
    }

}

